﻿"""
上传标准件数据到数据库
"""
# 导入需要的库
import pandas as pd
from sqlalchemy import create_engine
# import pymssql
import pymssql
from sortingdataprocess import get_test_data
from datetime import datetime

# 创建SQL server 数据库连接
engine = create_engine(
    "mssql+pyodbc://peapp:peapp1@172.18.65.31,1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server")

# 显示所有的列 方便调试
pd.set_option('display.max_columns', None)


def upload_stddata(standpartID, user_id, note, stddata, machine_id, MoID, logger, pluse):
    if note == '一次性':
        # 增加对订单的长度的验证
        print(type(MoID), len(MoID))
        if type(MoID) == str and len(MoID) == 13:
            MoID = MoID
        else:
            return -1
    else:
        # 不是一次性的,清除之前一次性保存的MoID
        MoID = ''
    df2 = pd.DataFrame()
    if machine_id == 14 and not pluse:
        df2 = stddata.loc[:,
              ['TestNO', 'Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm', 'CIEx', 'CIEy', 'Ra', 'R9', 'TestTime',
               'TestTemperature', 'RadiantFlux_mW']]
        df2 = df2.rename(columns={'TestNO': 'StandardPartNO'})
        df2['DataStatus'] = -1
        df2['UpdateTime'] = datetime.now()
        df2['StandardPartID'] = standpartID
        df2['UserID'] = user_id
        df2['StandardPart_Type'] = note
        df2['MachineID'] = machine_id
        df2['MoID'] = MoID
    elif machine_id == 14 and pluse:
        df2 = stddata.loc[:,
              ['TestNO', 'Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm', 'CIEx', 'CIEy', 'Ra', 'R9', 'TestTime',
               'TestTemperature', 'RadiantFlux_mW', 'u5', 'u6']]
        df2 = df2.rename(columns={'TestNO': 'StandardPartNO'})
        df2['DataStatus'] = -1
        df2['UpdateTime'] = datetime.now()
        df2['StandardPartID'] = standpartID
        df2['UserID'] = user_id
        df2['StandardPart_Type'] = note
        df2['MachineID'] = machine_id
        df2['MoID'] = MoID
    elif machine_id == 16:
        df2 = stddata.loc[:,
              ['TestNO', 'Current_mA', 'ForwardVoltage_V', 'LuminousFlux_lm', 'CIEx', 'CIEy', 'Ra', 'R9', 'TestTime']]
        # print(df2)
        df2 = df2.rename(columns={'TestNO': 'StandardPartNO'})
        df2['DataStatus'] = 1
        df2['UpdateTime'] = datetime.now()
        df2['StandardPartID'] = standpartID
        df2['UserID'] = user_id
        df2['StandardPart_Type'] = note
        df2['MachineID'] = machine_id
        df2['MoID'] = MoID
    # print(df2)
    # df2.to_sql('StandardPartData', engine, if_exists='append', index=None)
    try:
        df2.to_sql('StandardPartData', engine, if_exists='append', index=False)
        # print(df2['MoID'])
        return 1
    except Exception as e:
        logger.error(e + 'gtfyghu')
        return -2


def add_moid(StandardPartID, MoID, moid):
    conn = pymssql.connect(
        # DRIVER='{SQL SERVER}',
        server='172.18.65.31',
        database='SortingDB',
        user='peapp',
        password='peapp1'
    )
    cursor = conn.cursor(as_dict=True)
    cursor.execute(
        f"SELECT ID FROM [dbo].[StandardPartData] WHERE StandardPartID='{StandardPartID}' and DataStatus=1")
    row = cursor.fetchall()
    id_list = []
    for i in row:
        id_list.append(i['ID'])
    print(id_list)
    for id in id_list:
        str2 = moid
        if MoID in moid:
            return
        else:
            str2 = moid + "," + MoID if moid else MoID
        cursor.execute(f"UPDATE StandardPartData SET MoID='{str2}' WHERE ID={id}")
    conn.commit()
    conn.close()


if __name__ == '__main__':
    # print(get_test_data(r'..\\..\\lot6.xls',14))
    print(upload_stddata('AAAAA', '10223', get_test_data(r'..\\..\\lot6.xls', 14)))
